<?php

namespace App\Exports;

use App\Models\CommodityActivitySkuStatistics;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Carbon;
use Maatwebsite\Excel\Concerns\FromQuery;

class ProfitStatisticsExport extends BaseExport implements FromQuery
{
    public function headings(): array
    {
        return [
            '序号',
            '商品编号',
            '活动编号',
            '商品名称',
            '规格',
            '商品添加时间',
            '活动抢购时间',
            '供应商',
            '订单总数',
            '退款总数',
            '退货总数',
            '实际订单总数',
            '成本单价',
            '成本总金额',
            '大咖单价',
            '大咖出货量',
            '大咖总金额',
            '精英单价',
            '精英出货量',
            '精英总金额',
            '群主单价',
            '群主出货量',
            '群主总金额',
            '直邀金额',
            '直邀数量',
            '直邀总金额',
            '间邀金额',
            '间邀数量',
            '间邀总金额',
            '大咖与精英差价佣金',
            '大咖与精英数量',
            '大咖与精英总佣金',
            '精英与群主差价佣金',
            '精英与群主数量',
            '精英与群主总佣金',
            '大咖与群主差价佣金',
            '大咖与群主数量',
            '大咖与群主总佣金',
            '总收入',
            '总成本',
            '总佣金',
            '总直邀/间邀奖励',
            '利润',
        ];
    }

    public function map($row): array
    {
        $row = $row->toArray();

        $incomeFromStrongman = $row['strongman_shipments_amount']; // 大咖出货总金额
        $incomeFromElite = $row['elite_shipments_amount']; // 精英出货总金额
        $incomeFromGroupOwner = $row['group_owner_shipments_amount']; // 群主出货总金额
        $totalIncome = $incomeFromStrongman + $incomeFromElite + $incomeFromGroupOwner; // 总收入

        // 成本价 * 各个等级出货量
        $costFromStrongman = $row['strongman_shipments_purchase_amount']; // 大咖出货成本价
        $costFromElite = $row['elite_shipments_purchase_amount']; // 精英出货成本价
        $costFromGroupOwner = $row['group_owner_shipments_purchase_amount']; // 群主出货成本价
        $totalCost = $costFromStrongman + $costFromElite + $costFromGroupOwner;

        $commissionFromStrongmanElite = $row['number_of_strongman_with_elite_commissions_amount']; // 大咖与精英总佣金
        $commissionFromEliteGroupOwner = $row['number_of_elite_with_group_owner_commissions_amount']; // 精英与群主总佣金
        $commissionFromStrongmanGroupOwner = $row['number_of_strongman_with_group_owner_commissions_amount']; // 大咖与群主总佣金
        $totalCommission = $commissionFromStrongmanElite + $commissionFromEliteGroupOwner + $commissionFromStrongmanGroupOwner; // 总佣金

        $directInvitations = $row['number_of_direct_invitations_amount']; // 直接邀请
        $indirectInvitations = $row['number_of_indirect_invitations_amount']; // 间接邀请
        $totalInvitations = $directInvitations + $indirectInvitations; // 总邀请奖励金额

        return [
            $row['id'], // 序号
            $row['numbering'], // 商品编号
            $row['commodity_activity_numbering'], // 活动编号
            $row['commodity_name'], // 商品名称
            implode('-', array_map(fn ($value) => $value['content'], $row['commodity_activity_sku']['values'])), // 规格
            $row['commodity_created_at'], // 商品添加时间
            implode('~', [$row['panic_buying_start_time'] ?? '', $row['panic_buying_end_time'] ?? '']), // 活动抢购时间
            $row['supplier_name'], // 供应商
            (string) $row['quantity_sold'], // 订单总数
            (string) $row['total_refunds'], // 退款总数
            (string) $row['total_returns'], // 退货总数
            (string) ($row['quantity_sold'] - $row['total_returns'] - $row['total_refunds']), // 实际订单总数
            number_format($row['commodity_activity_sku']['purchase_price'] / 100, 2), // 成本单价
            number_format(($row['quantity_sold_amount'] - $row['total_returns_amount'] - $row['total_refunds_amount']) / 100, 2), // 成本总金额
            number_format($row['commodity_activity_sku']['strongman_price'] / 100, 2), // 大咖单价
            (string) $row['strongman_shipments'], // 大咖出货量
            number_format($row['strongman_shipments_amount'] / 100, 2), // 大咖总金额
            number_format($row['commodity_activity_sku']['elite_price'] / 100, 2), // 精英单价
            (string)$row['elite_shipments'], // 精英出货量
            number_format($row['elite_shipments_amount'] / 100, 2), // 精英总金额
            number_format($row['commodity_activity_sku']['group_owner_price'] / 100, 2), // 群主单价
            (string) $row['group_owner_shipments'], // 群主出货量
            number_format($row['group_owner_shipments_amount'] / 100, 2), // 群主总金额
            number_format($row['commodity_activity_sku']['direct_invitation'] / 100, 2), // 直邀金额
            (string) $row['number_of_direct_invitations'], // 直邀数量
            number_format($row['number_of_direct_invitations_amount'] / 100, 2), // 直邀总金额
            number_format($row['commodity_activity_sku']['indirect_invitation'] / 100, 2), // 间邀金额
            (string) $row['number_of_indirect_invitations'], // 间邀数量
            number_format($row['number_of_indirect_invitations_amount'] / 100, 2), // 间邀总金额
            number_format(($row['commodity_activity_sku']['elite_price'] - $row['commodity_activity_sku']['strongman_price']) / 100, 2), // 大咖与精英差价佣金
            (string) $row['number_of_strongman_with_elite_commissions'], // 大咖与精英数量
            number_format($row['number_of_strongman_with_elite_commissions_amount'] / 100, 2), // 大咖与精英总佣金
            number_format(($row['commodity_activity_sku']['group_owner_price'] - $row['commodity_activity_sku']['elite_price']) / 100, 2), // 精英与群主差价佣金
            (string) $row['number_of_elite_with_group_owner_commissions'], // 精英与群主数量
            number_format($row['number_of_elite_with_group_owner_commissions_amount'] / 100, 2), // 精英与群主总佣金
            number_format(($row['commodity_activity_sku']['group_owner_price'] - $row['commodity_activity_sku']['strongman_price']) / 100, 2), // 大咖与群主差价佣金
            (string) $row['number_of_strongman_with_group_owner_commissions'], // 大咖与群主数量
            number_format($row['number_of_strongman_with_group_owner_commissions_amount'] / 100, 2), // 大咖与群主总佣金
            number_format($totalIncome / 100, 2), // 总收入
            number_format($totalCost / 100, 2), // 总成本
            number_format($totalCommission / 100, 2),  // 总佣金
            number_format($totalInvitations / 100, 2), // 总直间邀请奖励
            number_format(($totalIncome - $totalCost - $totalCommission - $totalInvitations) / 100, 2), // 利润
        ];
    }

    public function query()
    {
        return CommodityActivitySkuStatistics::query()
            ->join('commodities', 'commodities.id', '=', 'commodity_activity_sku_statistics.commodity_id')
            ->join('commodity_activities', 'commodity_activities.id', '=', 'commodity_activity_sku_statistics.commodity_activity_id')
            ->join('suppliers', 'suppliers.id', '=', 'commodities.supplier_id')
            ->with('commodityActivitySku.values')
            ->when($this->request['numbering'] ?? '', function (Builder $builder, $numbering) {
                $builder->where('commodities.numbering', 'like', "$numbering%");
            })
            ->when($this->request['activity_numbering'] ?? '', function (Builder $builder, $activityNumber) {
                $builder->where('commodity_activities.numbering', 'like', "$activityNumber%");
            })
            ->when($this->request['commodity_name'] ?? '', function (Builder $builder, $name) {
                $builder->where('commodities.name', 'like', "$name%");
            })
            ->when($this->request['supplier_name'] ?? '', function (Builder $builder, $supplierName) {
                $builder->where('suppliers.name', 'like', "$supplierName%");
            })
            ->when($this->request['created_at_begin'] ?? '', function (Builder $builder, $createdAtBegin) {
                $builder->where('commodities.created_at', '>=', $createdAtBegin);
            })
            ->when($this->request['created_at_end'] ?? '', function (Builder $builder, $createdAtEnd) {
                $builder->where('commodities.created_at', '<=', Carbon::parse($createdAtEnd)->endOfDay());
            })
            ->when($this->request['panic_buying_start_time'] ?? '' && $this->request['panic_buying_end_time'] ?? '', function (Builder $builder) {
                $betweenArr = [
                    Carbon::parse($this->request['panic_buying_start_time'] ?? '')->startOfDay()->format('Y-m-d H:i:s'),
                    Carbon::parse($this->request['panic_buying_end_time'] ?? '')->endOfDay()->format('Y-m-d H:i:s')
                ];
                $builder->whereBetween('commodity_activities.panic_buying_start_time', $betweenArr);
            })
            ->orderByDesc('created_at')
            ->select([
                'commodity_activity_sku_statistics.*',
                'commodities.numbering',
                'commodities.created_at as commodity_created_at',
                'commodities.name as commodity_name',
                'commodity_activities.numbering as commodity_activity_numbering',
                'commodity_activities.panic_buying_start_time',
                'commodity_activities.panic_buying_end_time',
                'suppliers.name as supplier_name',
            ]);
    }
}
